#!/bin/bash -e

USER="oss_admin"
DATABASE="oss"
PASSWORD='123'
FINISH_GUIDE=3
MYSQL_FILE_DIR="/var/lib/mysql-files"

MYSQL=`which mysql`
total_login=`$MYSQL -u $USER --password=$PASSWORD  --show-warnings=false << EOF | tail -n +2
use $DATABASE;
select count(uid) from PlayerRegister;
EOF`
echo total login:$total_login
echo

total_robot=`$MYSQL -u $USER --password=$PASSWORD  --show-warnings=false << EOF | tail -n +2
use $DATABASE;
select count(uid) from PlayerRegister where AccountType = "4";
EOF`
echo total robot:$total_robot
echo

NOW_TIMESTAMP=`date +%s`
YESTERDAY=$((NOW_TIMESTAMP - 86400))
LAST_DATE=`date -d @$YESTERDAY +"%Y-%m-%d %H:%M:%S"`
HOUR=`date -d @$NOW_TIMESTAMP +"%H"`

echo last date:$LAST_DATE
today_login=`$MYSQL -u $USER --password=$PASSWORD  --show-warnings=false << EOF | tail -n +2
use $DATABASE;
select count(distinct uid) from PlayerLogin where EventTime > "$LAST_DATE" and AccountType != "4";
EOF`
echo today login:$today_login
echo

today_register=`$MYSQL -u $USER --password=$PASSWORD  --show-warnings=false << EOF | tail -n +2
use $DATABASE;
select count(uid) from PlayerRegister where EventTime > "$LAST_DATE" and AccountType != "4";
EOF`
echo today register:$today_register
echo

online_info=`$MYSQL -u $USER --password=$PASSWORD  --show-warnings=false << EOF | tail -n +2
use $DATABASE;
select EventTime,TotalOnlineCount,Level1OnlineCount,Level2OnlineCount,Level3OnlineCount,Level4OnlineCount,Level5OnlineCount,Level6OnlineCount,Level7OnlineCount,Level8OnlineCount,Level9OnlineCount,Level10OnlineCount from PlayerOnline order by EventTime desc limit 1;
EOF`

echo online info:$online_info
echo

finish_guide=`$MYSQL -u $USER --password=$PASSWORD  << EOF | tail -n +2
use $DATABASE;
select count(distinct uid) from PlayerGuide where step >= $FINISH_GUIDE;
EOF`
echo finish guide:$finish_guide
echo

#select uid, count(*) lcount from PlayerLogin where Network = 1 group by uid;
#select uid, count(*) lcount from PlayerLogin where Network = 2 group by uid;
net_info=`$MYSQL -u $USER --password=$PASSWORD  << EOF | tail -n +2
use $DATABASE;
select n.NetName, t.ucount from (select Network, count(distinct uid) ucount from PlayerLogin where AccountType != "4" group by Network) as t left join PlayerLogin_NetType n on n.netid = t.network;
EOF`
echo network info:$net_info
echo

match_info=`$MYSQL -u $USER --password=$PASSWORD  << EOF | tail -n +2
use $DATABASE;
select t.MatchName, g.rcount from (select GameType, count(distinct roomid) rcount from PlayerStartGame group by GameType) as g left join PlayerStartGame_MatchType t on t.MatchId = g.GameType;
EOF`
echo match info:$match_info
echo

game_end_reason=`$MYSQL -u $USER --password=$PASSWORD  << EOF | tail -n +2
use $DATABASE;
select t.ReasonName, g.rcount from (select Reason ,count(distinct RoomId) rcount from PlayerEndGame group by Reason) as g left join PlayerEndGame_ResultReason t on t.ReasonId = g.Reason;
EOF`
echo game end reason: $game_end_reason
echo

player_will_share=`$MYSQL -u $USER --password=$PASSWORD  << EOF | tail -n +2
use $DATABASE;
select count(distinct uid) from PlayerShare;
EOF`
echo player will share: $player_will_share
echo

player_share_count=`$MYSQL -u $USER --password=$PASSWORD  << EOF | tail -n +2
use $DATABASE;
select p.uid, p.name, s.ucount from (select uid,count(uid) ucount from PlayerShare group by uid) as s left join (select distinct uid, name from PlayerShare) as p on p.uid = s.uid into outfile "$MYSQL_FILE_DIR/player_share_count.txt FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n";
EOF`
echo player share count file : $MYSQL_FILE_DIR/player_share_count.txt
echo

